package model;

import java.sql.*;
import java.util.Vector;

import javax.swing.table.*;

/**
 * 
 * @author Steven
 * 
 * Passes SQL statements to server: query, create, and delete. 
 * Creates DefaultTableModels for each graphic table for UI.
 * Stores information about drink/producer currently being added to database.
 *
 */
public class DatabaseCommunicator {
	
	private Connection connection;
	public static DatabaseCommunicator dbc;
	public String currAddType = "soup";
	public String currDrink = "water";
	public String currProducer = "Qualm";
	
	public DatabaseCommunicator(Connection connection){
		this.connection = connection;
	}
	
	public DefaultTableModel populateTable(String tableName){
		try {
			Statement stmt = connection.createStatement();
			//ResultSet rs = stmt.executeQuery("describe "+tableName);
			ResultSet rs = stmt.executeQuery("SELECT * FROM "+tableName);
			return resultSetToTableModel(rs);
			
		} catch (SQLException ex) {
    	    // handle any errors
    	    System.out.println("SQLException: " + ex.getMessage());
    	    System.out.println("SQLState: " + ex.getSQLState());
    	    System.out.println("VendorError: " + ex.getErrorCode());
		}
		
		return null;
		
	}
	
	public void query(String info){
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(info);
			while ( rs.next() ) {
				System.out.println(rs.getString(1));
			}
		} catch (SQLException ex) {
    	    // handle any errors
    	    System.out.println("SQLException: " + ex.getMessage());
    	    System.out.println("SQLState: " + ex.getSQLState());
    	    System.out.println("VendorError: " + ex.getErrorCode());
		}
		
	}
	
	public void delete(String info){
		try {
			Statement stmt = connection.createStatement();
			int rs = stmt.executeUpdate(info);
			System.out.println("Deletion " + rs);
		} catch (SQLException ex) {
    	    // handle any errors
    	    System.out.println("SQLException: " + ex.getMessage());
    	    System.out.println("SQLState: " + ex.getSQLState());
    	    System.out.println("VendorError: " + ex.getErrorCode());
		}
	}
	
	public void create(String info){
		try {
			Statement stmt = connection.createStatement();
			int rs = stmt.executeUpdate(info);
			System.out.println("Creation " + rs);
		} catch (SQLException ex) {
    	    // handle any errors
    	    System.out.println("SQLException: " + ex.getMessage());
    	    System.out.println("SQLState: " + ex.getSQLState());
    	    System.out.println("VendorError: " + ex.getErrorCode());
		}
	}
	
	public void update(String info){
		try {
			Statement stmt = connection.createStatement();
			int rs = stmt.executeUpdate(info);
			System.out.println("Update " + rs);
		} catch (SQLException ex) {
    	    // handle any errors
    	    System.out.println("SQLException: " + ex.getMessage());
    	    System.out.println("SQLState: " + ex.getSQLState());
    	    System.out.println("VendorError: " + ex.getErrorCode());
		}
	}
	
	public static DefaultTableModel resultSetToTableModel(ResultSet rs) {
        try {
            ResultSetMetaData metaData = rs.getMetaData();
            int numberOfColumns = metaData.getColumnCount();
            Vector columnNames = new Vector();

            // Get the column names
            for (int column = 0; column < numberOfColumns; column++) {
                columnNames.addElement(metaData.getColumnLabel(column + 1));
            }

            // Get all rows.
            Vector rows = new Vector();

            while (rs.next()) {
                Vector newRow = new Vector();

                for (int i = 1; i <= numberOfColumns; i++) {
                    newRow.addElement(rs.getObject(i));
                }

                rows.addElement(newRow);
            }

            return new DefaultTableModel(rows, columnNames);
        } catch (Exception e) {
            e.printStackTrace();

            return null;
        }
    }
	
	public void setConnection(Connection conn) {
		connection = conn;
	}
	

}
